--TRIGGERS AND FUNCTION 

CREATE OR REPLACE FUNCTION "eliminarSensor"()
  RETURNS trigger AS
$BODY$begin
DELETE FROM SENSOR WHERE ID_SENSOR = old.id_sensor;
return null;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION "eliminarSensor"()
  OWNER TO postgres;

  -- Trigger: dispatchEventEliminarSensorPiso on sensor_piso

CREATE TRIGGER "dispatchEventEliminarSensorPiso"
  AFTER DELETE
  ON sensor_piso
  FOR EACH ROW
  EXECUTE PROCEDURE "eliminarSensor"();

  -- Trigger: dispatchEventEliminarSensor on sensor_edificio

CREATE TRIGGER "dispatchEventEliminarSensor"
  AFTER DELETE
  ON sensor_edificio
  FOR EACH ROW
  EXECUTE PROCEDURE "eliminarSensor"();

  -- Trigger: dispatchEventEliminarSenorZona on sensor_zona

CREATE TRIGGER "dispatchEventEliminarSenorZona"
  AFTER DELETE
  ON sensor_zona
  FOR EACH ROW
  EXECUTE PROCEDURE "eliminarSensor"();
  
  -- ALERTA FUNCTION
	CREATE OR REPLACE FUNCTION insertarAlerta()
		RETURNS trigger as
		$BODY$
		DECLARE
		fecha_consulta timestamp;
		medicion_anterior real;
		suma_mediciones real:=0;
		fecha_anterior timestamp;
		promedio real;
		BEGIN
			FOR i in 1..10 loop
			  fecha_anterior:= new.fecha_hora - interval '1 week'*i;
			  medicion_anterior := (SELECT valor_medicion FROM medicion_sensor_test WHERE id_sensor = new.id_sensor and fecha_hora = fecha_anterior);
			  suma_mediciones := suma_mediciones + medicion_anterior; 	
			end loop;
			promedio := (suma_mediciones/10)*1.1;
			if ( new.valor_medicion > promedio )THEN
			   INSERT INTO alerta(id_sensor, valor_medicion, valor_medicion_promedio, fecha_hora) 
		                    VALUES(new.id_sensor, new.valor_medicion, promedio , new.fecha_hora);
		        end if;
			return null;
		END
		$BODY$
		LANGUAGE plpgsql VOLATILE
	  COST 100;
	  ALTER FUNCTION insertarAlerta()
	  OWNER TO postgres;
	
	--TRIGGER
	CREATE TRIGGER "BUSCARALERTA"
        AFTER INSERT
        ON medicion_sensor_test
        FOR EACH ROW
        EXECUTE PROCEDURE insertarAlerta();


